package com.itm.lqm.dao;

import java.sql.Connection;



import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import com.itm.lqm.entity.Grade;
import com.itm.lqm.util.BaseDao;



public class GradeDao {

	//查询成绩
			public List<Grade> findGradeAll(){
				try{
					List<Grade> list=new ArrayList<Grade>();
				Connection conn=BaseDao.getConnection();
				PreparedStatement ps=conn.prepareStatement("select gid,usualG,finalG,overallG,sname, cname,sno,clas from  courses,grade,student where  courses.cid=grade.cid and student.sid=grade.sid ");
				 ResultSet rs=ps.executeQuery();
				 Grade g=null;
				 while(rs.next()){
					 g=new Grade();
					 g.setGid(rs.getInt("gid"));
					 g.setUsualG(rs.getInt("usualG"));
					 g.setFinalG(rs.getInt("finalG"));
					g.setOverallG(rs.getInt("overallG"));
					g.setSname(rs.getString("sname"));
					g.setCname(rs.getString("cname"));
					g.setSno(rs.getString("sno"));
					g.setClas(rs.getString("clas"));
				     list.add(g);
					 System.out.println(g);
				 }
				 return list;
				 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			}
			
			
			
			
			
			public List<Grade> findGradeByClass(String clas){
				try{
					List<Grade> list=new ArrayList<Grade>();
				Connection conn=BaseDao.getConnection();
				PreparedStatement ps=conn.prepareStatement("select gid,sname, cname,sno,clas from  courses,grade,student where  courses.cid=grade.cid and student.sid=grade.sid and clas=?");
				ps.setString(1,clas );
				ResultSet rs=ps.executeQuery();
				 Grade g=null;
				 while(rs.next()){
					 g=new Grade();
					 g.setGid(rs.getInt("gid"));
					g.setSname(rs.getString("sname"));
					g.setCname(rs.getString("cname"));
					g.setSno(rs.getString("sno"));
					g.setClas(rs.getString("clas"));
				     list.add(g);
					 System.out.println(g);
				 }
				 return list;
				 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
			}
			
			
			
			
			
			//添加成绩
			public boolean addGrade(Grade g){
				try{
					Connection con=BaseDao.getConnection();
					PreparedStatement pstm=con.prepareStatement("insert into grade(gid,usualG,finalG,overallG,sname, cname,sno,clas ) values(?,?,?,?,?,?,?,?)");
					pstm.setInt(1,g.getGid() );
					pstm.setInt(2,g.getUsualG() );
					pstm.setInt(3,g.getFinalG() );
					pstm.setInt(4,g.getOverallG());
					pstm.setString(5,g.getSname());;
					pstm.setString(6, g.getCname());
					pstm.setString(7, g.getSno());
					pstm.setString(8, g.getClas());
					int rs = pstm.executeUpdate();
					
					if(rs>0){
						return true;
					}else{
						return false;
					}
				}catch(Exception e){
					e.printStackTrace();
					return false;
				}
			}
			//修改成绩
			public boolean updateGrade(Grade g){
				try{ //update courses set cid=?,cno=?,cname=?,tid=?,taddress=? where cid=?"
					Connection con=BaseDao.getConnection();
					PreparedStatement pstm=con.prepareStatement("update grade set gid=?,usualG=?,finalG=?,overallG=? where gid=?");
					//pstm.setInt(1, c.getCid());
					
					pstm.setInt(1,g.getGid() );
					pstm.setInt(2,g.getUsualG() );
					pstm.setInt(3,g.getFinalG() );
					pstm.setInt(4,g.getOverallG());
//					pstm.setString(5,g.getSname() );
//					pstm.setString(6,g.getCname() );
					pstm.setInt(5,g.getGid() );
		            int rs = pstm.executeUpdate();
		            System.out.println(rs);
		            if(rs>0){
						return true;
					}else{
						return false;
					}
				}catch(Exception e){
					e.printStackTrace();
					return false;
				}
			}
			//删除成绩
			public boolean deleteGradeByGid(int gid){
				try{
					Connection con=BaseDao.getConnection();
					PreparedStatement pstm=con.prepareStatement("delete from grade where gid=?");
					pstm.setInt(1, gid);
					int rs=pstm.executeUpdate();
					if(rs>0){
						return true;
					}else{
						return false;
					}
				}catch(Exception e){
					e.printStackTrace();
					return false;
				}
			}
			/*
			 * 验证该商品是否存在<根据id>
			 */
			public boolean isExistGid(int gid){
				return findGradeByGid(gid)==null?false:true;
			}
			/*
			 * 验证该商品是否存在<根据name>
			 */
//			public boolean isExist(String name){
//				return findBookByName(name)==null?false:true;
//			}
			/*
			 * 根据id查询相应商品
			 */
			public Grade findGradeByGid(int cid){
				try{
					Connection con=BaseDao.getConnection();
					PreparedStatement pstm=con.prepareStatement("select gid,usualG,finalG,overallG,sname, cname from  course,grade,student where  course.cid=grade.cid and student.sid=grade.sid and gid=?");
					//pstm.set(1, book_id);
					pstm.setInt(1, cid);
					ResultSet rs=pstm.executeQuery();
					Grade g=null;
					while(rs.next()){
						g=new Grade();
						 g.setGid(rs.getInt("gid"));
						 g.setUsualG(rs.getInt("usualG")); 
						 g.setFinalG(rs.getInt("finalG")); 
						 g.setOverallG(rs.getInt("overallG")); 
						 g.setSname(rs.getString("sname")); 
						 g.setCname(rs.getString("cname")); 
						
					}
					return g;
				}catch(Exception e){
					e.printStackTrace();
					return null;
				}
			}
			
			public Grade findGradeBygid(int gid){
				try{
					Connection con=BaseDao.getConnection();
					PreparedStatement pstm=con.prepareStatement("select gid,usualG,finalG,overallG,sname, cname,sno,clas  from  course,grade,student where  course.cid=grade.cid and student.sid=grade.sid and gid=?");
					//pstm.set(1, book_id);
					pstm.setInt(1, gid);
					ResultSet rs=pstm.executeQuery();
					Grade g=null;
					while(rs.next()){
						g=new Grade();
						 g.setGid(rs.getInt("gid"));
						 g.setUsualG(rs.getInt("usualG"));
						 g.setFinalG(rs.getInt("finalG"));
						g.setOverallG(rs.getInt("overallG"));
						g.setSname(rs.getString("sname"));
						g.setCname(rs.getString("cname"));
						g.setSno(rs.getString("sno"));
						g.setClas(rs.getString("clas"));
						
					}
					return g;
				}catch(Exception e){
					e.printStackTrace();
					return null;
				}
			}
			
			
}
